SQL Reference

The SQL language that is used for filtering journal entries is very similar to the IBM SQL. In most cases it should be possible to use the same SQL statements that would be used on the host system.

Field Names

Field names must start with a letter, followed by any number of digits or letters.

VALUES_WITH_TAX = 47.50

Special Letters

Field name may include the following special letters:

For any other special letters, such as letter £ (pound), field names be be encapsulated in double quotes.

"VALUES_IN_£" = 47.50

Type Casting

Strings are automatically casted into Number, Date, Time and Timestamp values.

Date formats

ISOYYYY-MM-DD
USAMM/DD/YYYY
EURDD.MM.YYYY

Time formats

ISOHH24.MI.SS
USA (am)HH12:MI AM
USA (pm)HH12:MI PM
EURHH24:MI:SS

Timestamp formats

ISOYYYY-MM-DD HH24:MI:SS.NNN
IBMYYYY-MM-DD-HH24.MI.SS.NNN

SQL Functions

Function Example
Abs Abs(7.59) = 7.59
Abs(-7.59) = 7.59
Add 7.59 + 2.41 = 10.0
Date('2016-02-27') + Day(5) = '2016-03-03'
Time('15.10.30') + Hour(15) = '06:10:30'
Timestamp('2018-12-21-15.10.30.123') + Hour(5) = '2018-12-21-20.10.30.123'
Between 5 Between 1 And 10
Date('2018-12-21') Between '2018-12-20' And '2018-12-22'
Time('15.10.30') Between '15.10.29' And '15.10.31'
Timestamp('2018-12-21-15.10.30') Between '2018-12-21-15.10.29' And '2018-12-21-15.10.31'
Ceil Ceil(10.5) = 11
Ceil(1.234e2) = 124
Concat Concat('Hello', '-World') = 'Hello-World'
'a' || 'b' || 'c' = 'abc'
Date Date('2018-12-05') = '2018-12-05'
Date('2018-12-05') > '2018-12-04'
Date(Timestamp('2018-12-05-15.30.00.123')) = '2018-12-05'
Day Day(Date('2018-12-05')) = 5
Date('2018-12-05') + Day(10) = '2018-12-15'
Date('2018-12-05') - Day(10) = '2018-11-25'
Divide 26 / 8 = 3.25
26 / '3.25' = 8
Floor Floor(10.5) = 10
Floor(1.234e2) = 123
Hour Hour(Time('12:00:00')) = 12
Time('12:00:00') + Hour(3) = '15:00:00'
Time('12:00:00') - Hour(3) = '09:00:00'
In 'PT' In('PT', 'PX', 'UB', 'UP', 'UR', 'DL', 'DR')
'2.2' In(1, 2.2, 3, 4.5, 5, 6)
Date('2018-12-20') In(Date('2018-12-19'), '2018-12-20', '2018-12-21')
Length Length('Hello World') = 11
Like 'Westend New York' Like('%New%') = true
'Westend New York' Like('__stend New York') = true
'Eastend New York' Like('__stend New York') = true

With regular expression (expression starts and ends with a slash):
'Westend New York' Like('/.*New.*/') = true

Lower Lower('Hello World') = 'hello world'
Lpad Lpad('Hello', 10) = ' Hello'
Lpad('Hello', 10, '-+') = '-+-+-Hello'
Ltrim LTrim(' It works! ') = 'It works! '
LTrim('***It works!###', '*') = 'It works!###'
LTrim('123It works!789', '0123456789') = 'It works!789'
MicrosecondMicrosecond(Timestamp('2018-12-05-15.00.55.123')) = 123
Minute Minute(Time('12:25:00')) = 25
Time('12:00:00') + Minute(65) = '13:05:00'
Time('12:00:00') - Minute(65) = '10:55:00'
Modulus 11 Mod 3 = 2
9 Mod 3 = 0
Month Month(Date('2018-12-05')) = 12
Date('2018-12-05') + Month(1) = '2019-01-05'
Date('2018-12-05') - Month(1) = '2018-11-05'
Multiply 3.25 * 8 = 26
8 * '3.25' = 26
Regexp_Like'Westend New York' Regexp_Like('.*New.*') = true
'Westend New York' Regexp_Like('.{2}stend New York') = true
'Eastend New York' Regexp_Like('.{2}stend New York') = true
Replace Replace('ZZZ-ABC-ZZZ-ABC-ZZZ', 'ABC', 'WWW') = 'ZZZ-WWW-ZZZ-WWW-ZZZ'
Replace('ZZZ-ABC-ZZZ-ABC-ZZZ', 'ABC') = 'ZZZ--ZZZ--ZZZ'
Round Round(26.4) = 26
Round(26.5) = 27

Round(125.432, 1) = 125.4
Round(124.567, 1) = 124.6

Round(125.432, 0) = 125
Round(124.567, 0) = 125

Round(125.432, -1) = 130
Round(124.567, -1) = 120

Round(Date('2018-07-01'), 'YYYY') = '2019-01-01'
Round(Date('2018-06-30'), 'YYYY') = '2018-01-01'
Round(Date('2016-02-16'), 'MM') = '2016-03-01'
Round(Date('2016-02-15'), 'MM') = '2016-02-01'

Round(Time('23:30:00'), 'HH24') = '00:00:00'
Round(Time('23:29:00'), 'HH24') = '23:00:00'
Round(Time('23:59:30'), 'MI') = '00:00:00'
Round(Time('23:59:30'), 'MI') = '00:00:00'

Rpad Rpad('Hello', 10) = 'Hello '
Rpad('Hello', 10, '-+') = 'Hello-+-+-'
Rtrim RTrim(' It works! ') = ' It works!'
RTrim('***It works!###', '#') = '***It works!'
RTrim('123It works!789', '0123456789') = '123It works!'
Second Second(Time('12:00:25')) = 25
Time('12:00:00') + Second(65) = '12:01:05'
Time('12:00:00') - Second(65) = '11:58:55'
Sign Sign(-10) = -1
Sign(0) = 0
Sign(10) = 1
Substract 2.41 - 10 = -7.59
Date('2016-03-03') - Day(5) = '2016-02-27'
Time('15.10.30') - Hour(5) = '10:10:30'
Timestamp('2018-12-21-15.10.30.123') - Hour(5) = '2018-12-21-10.10.30.123'
Substr Equivalent to: Substring
Substring Substring('Hello World', 1, 5) = 'Hello'
Substring('Hello World', 7) = 'World'
Substring('Hello World', 7, 999) = 'World'
Substring('Hello World', 999) = ''
Time Time('12:00:00') = '12:00:00'
Time('12:00:00') > '11:59:59'
Time(Timestamp('2018-12-05-15.30.00.123')) = '15.30.00'
Timestamp Timestamp('2018-12-05 12:15:25') = Date('2018-12-05') + Time('12:15:25')
Trim Trim(' It works! ') = 'It works!'
Trim('***It works!###', '*') = 'It works!###'
Trim('123It works!789', '0123456789') = 'It works!'
Upper Upper('Hello World') = 'HELLO WORLD'
Year Year(Date('2018-12-05')) = 2018
Date('2018-12-05') + Year(1) = '2019-12-05'
Date('2018-12-05') - Year(1) = '2017-12-05'